﻿/*
Created: 14/05/2014
Modified: 14/05/2014
Project: Quan Ly Ky Tuc Xa Sinh Vien
Model: KTXDB
Database: MS SQL Server 2012
*/


-- Create tables section -------------------------------------------------

-- Table Phong

CREATE TABLE [Phong]
(
 [PID] Int IDENTITY NOT NULL,
 [MaPhong] Varchar(10) NOT NULL unique,
 [SoGiuong] Int NULL,
 [TrangThai] Bit NOT NULL,
 [SoGiuongTrong] Int NULL,
 [ToaNha] Varchar(5) NULL,
 [GiaPhong] Money NULL
)
go

-- Add keys for table Phong

ALTER TABLE [Phong] ADD CONSTRAINT [PID] PRIMARY KEY ([PID])
go

-- Table ToaNha

CREATE TABLE [ToaNha]
(
 [TID] Varchar(5) NOT NULL,
 [TenToaNha] Nvarchar(30) NULL,
 [KyHieu] Varchar(5) NULL
)
go

-- Add keys for table ToaNha

ALTER TABLE [ToaNha] ADD CONSTRAINT [TID] PRIMARY KEY ([TID])
go

-- Table PhanQuyen

CREATE TABLE [PhanQuyen]
(
 [RID] Varchar(5) NOT NULL,
 [TenQuyen] Nvarchar(30) NOT NULL,
 [TrangThai] Bit NOT NULL
)
go

-- Add keys for table PhanQuyen

ALTER TABLE [PhanQuyen] ADD CONSTRAINT [Key3] PRIMARY KEY ([RID])
go

-- Table NguoiDung

CREATE TABLE [NguoiDung]
(
 [UID] Int IDENTITY NOT NULL,
 [TenNguoiDung] Varchar(30) NOT NULL unique,
 [MatKhau] Nvarchar(30) NOT NULL,
 [SID] Int NULL,
 [RID] Varchar(5) NULL,
 [CID] Int NULL
)
go

-- Add keys for table NguoiDung

ALTER TABLE [NguoiDung] ADD CONSTRAINT [UID] PRIMARY KEY ([UID])
go

-- Table SinhVien

CREATE TABLE [SinhVien]
(
 [SID] Int IDENTITY NOT NULL,
 [TenSinhVien] Nvarchar(50) NOT NULL,
 [NgaySinh] Datetime NULL,
 [Lop] Varchar(10) NOT NULL,
 [SoCMND] Varchar(9) NOT NULL,
 [MSSV] Varchar(10) NOT NULL
)
go

-- Add keys for table SinhVien

ALTER TABLE [SinhVien] ADD CONSTRAINT [SID] PRIMARY KEY ([SID])
go

-- Table SinhVienTaiPhong

CREATE TABLE [SinhVienTaiPhong]
(
 [PID] Int NOT NULL,
 [SID] Int NOT NULL,
 [Tu] Datetime NOT NULL,
 [Den] Datetime NULL
)
go

-- Add keys for table SinhVienTaiPhong

ALTER TABLE [SinhVienTaiPhong] ADD CONSTRAINT [SID_PID] PRIMARY KEY ([PID],[SID])
go

-- Table CanBoQuanLy

CREATE TABLE [CanBoQuanLy]
(
 [CID] Int IDENTITY NOT NULL,
 [TenCanBo] Nvarchar(50) NOT NULL,
 [NgaySinh] Datetime NULL,
 [SoCMND] Varchar(9) NOT NULL,
 [MaCanBo] Varchar(9) NOT NULL unique
)
go

-- Add keys for table CanBoQuanLy

ALTER TABLE [CanBoQuanLy] ADD CONSTRAINT [CIDCheck] PRIMARY KEY ([CID])
go


-- Table DichVu

CREATE TABLE [DichVu]
(
 [SVID] Varchar(5) NOT NULL,
 [Thang] Date NOT NULL,
 [TenDichVu] Nvarchar(30) NOT NULL,
 [GiaDichVu] Money NOT NULL,
 [DonVi] Nvarchar(10) NOT NULL
)
go

-- Add keys for table DichVu

ALTER TABLE [DichVu] ADD CONSTRAINT [SVID] PRIMARY KEY ([SVID])
go

-- Table CTDV

CREATE TABLE [CTDV]
(
 [SID] Int NOT NULL,
 [SVID] Varchar(5) NOT NULL,
 [Thang] Date NOT NULL,
 [SoLuong] Int NOT NULL
)
go

-- Add keys for table CTDV

ALTER TABLE [CTDV] ADD CONSTRAINT [SDVID] PRIMARY KEY ([SID],[SVID],[Thang])
go

-- Create relationships section ------------------------------------------------- 

ALTER TABLE [Phong] ADD CONSTRAINT [Relationship1] FOREIGN KEY ([ToaNha]) REFERENCES [ToaNha] ([TID])
go

ALTER TABLE [NguoiDung] ADD CONSTRAINT [Relationship2] FOREIGN KEY ([SID]) REFERENCES [SinhVien] ([SID])
go

ALTER TABLE [NguoiDung] ADD CONSTRAINT [Relationship3] FOREIGN KEY ([RID]) REFERENCES [PhanQuyen] ([RID])
go

ALTER TABLE [SinhVienTaiPhong] ADD CONSTRAINT [Relationship4] FOREIGN KEY ([PID]) REFERENCES [Phong] ([PID])
go

ALTER TABLE [SinhVienTaiPhong] ADD CONSTRAINT [Relationship5] FOREIGN KEY ([SID]) REFERENCES [SinhVien] ([SID])
go

ALTER TABLE [NguoiDung] ADD CONSTRAINT [Relationship6] FOREIGN KEY ([CID]) REFERENCES [CanBoQuanLy] ([CID])
go

ALTER TABLE [CTDV] ADD CONSTRAINT [Relationship9] FOREIGN KEY ([SID]) REFERENCES [SinhVien] ([SID])
go

ALTER TABLE [CTDV] ADD CONSTRAINT [Relationship10] FOREIGN KEY ([SVID]) REFERENCES [DichVu] ([SVID])
go



